package com.serviceprovider.business.module.order.services.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.stereotype.Repository;

import com.serviceprovider.business.module.admin.user.dao.impl.UserMapper;
import com.serviceprovider.business.module.common.Page;
import com.serviceprovider.business.module.common.PaginationHelper;
import com.serviceprovider.business.module.order.model.Order;
import com.serviceprovider.business.module.order.model.OrderSearchCriteria;
import com.serviceprovider.business.module.order.services.dao.interfaces.IOrderDao;
import com.serviceprovider.business.module.order.services.dao.interfaces.IOrderStatusDao;
import com.serviceprovider.business.module.order.valueobjects.OrderFilterConditionVO;
import com.serviceprovider.business.module.user.model.impl.User;
import com.serviceprovider.business.utility.maintenance.settings.AppSettings;
import com.serviceprovider.business.utility.maintenance.settings.RoleUtils;

/**
 * DAO class which is used to work with Order
 *
 * @author mrduongnv@gmail.com
 */
@Repository("orderDao")
public class OrderDaoImpl extends SimpleJdbcDaoSupport implements IOrderDao {

    @Autowired
    private IOrderStatusDao orderStatusDao;
    private Logger logger = Logger.getLogger(getClass());

    @Autowired
    public OrderDaoImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    private class OrderMapper implements ParameterizedRowMapper<Order> {

        @Override
        public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
            Order order = new Order();
            order.setId(rs.getInt("order_id"));
            order.setName(rs.getString("name"));
            order.setDate(rs.getString("date"));
            order.setOrderStatus(orderStatusDao.get(rs
                    .getInt("order_status_id")));
            order.setMode(rs.getInt("shipping_mode"));
            order.setTips(rs.getString("shipping_tips"));
            order.setInstructions(rs.getString("shipping_instructions"));
            User user = new User();
            user.setId(rs.getInt("order_assigned_to"));
            order.setOrderAssignedTo(user);
            order.setServiceProviderId(rs.getInt("user_id"));
            order.setCreatedDate(rs.getDate("date_created"));
            return order;
        }

    }

    @Override
    public Page<Order> findAll(int pageNumber, int pageSize, String sortColumn,
                               String sortOrder) {
        PaginationHelper<Order> ph = new PaginationHelper<Order>();
        String sqlCount = "SELECT count(*) FROM order_details";
        String sqlQuery = "SELECT * FROM order_details order by " + sortColumn
                + " " + sortOrder;
        Object[] args = {};
        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args,
                pageNumber, pageSize, sortColumn, sortOrder, new OrderMapper());
    }

    @Override
    public void updateStatus(int orderid, int statusid) {
        getJdbcTemplate().update(
                "update order_details set order_status_id=? where order_id=?",
                new Object[]{statusid, orderid});

    }

    @Override
    public void updateAssignedUserIds(Integer orderId, Integer assignedUserId) {
        getJdbcTemplate()
                .update(
                        "update order_details set order_assigned_to=? where order_id=?",
                        new Object[]{assignedUserId, orderId});

    }

    @Override
    public Order findOrderById(int id) {
        String sql = "select * from order_details where order_id=" + id;
        try {
            return (Order) getJdbcTemplate().queryForObject(sql,
                    new OrderMapper());
        } catch (Exception e) {
            return null;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Order> findOrderByUserId(int userId) {
        String sql = "select * from order_details where user_id=" + userId;
        return getJdbcTemplate().query(sql, new OrderMapper());
    }

    @Override
    public Order findOrderByBatch(int bacthId) {
        String sql = "select * from order_details o, batch b where o.order_id=b.order_id and b.batch_id="
                + bacthId;
        return (Order) getJdbcTemplate().queryForObject(sql, new OrderMapper());
    }

    @Override
    public Order findOrderByTransactionId(int transactionId) {
        String sql = "select * from order_details o, batch b, transaction_details t where o.order_id=b.order_id and b.batch_id=t.batch_id and t.transaction_id="
                + transactionId;
        return (Order) getJdbcTemplate().queryForObject(sql, new OrderMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Order> findOrderByshippingMode(String shippingMode) {
        String sql = "select * from order_details where shipping_mode=?";
        return getJdbcTemplate().query(sql, new Object[]{shippingMode},
                new OrderMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Order> findOrderByOrderStatus(String orderStatusName) {
        String sql = "select * from order_details o,order_statuses s where o.order_status_id=s.id and s.name=?";
        return getJdbcTemplate().query(sql, new Object[]{orderStatusName},
                new OrderMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Order> findOrderByDateRange(String startDate, String endDate) {
        StringBuilder sql = new StringBuilder(
                "select * from order_details od where od.date BETWEEN ? and ?\n");
        List<Object> parameters = new ArrayList<Object>();
        parameters.add(startDate);
        parameters.add(endDate);
        return getJdbcTemplate().query(sql.toString(), parameters.toArray(),
                new OrderMapper());
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<Order> findOrderBySearchCriteria(OrderSearchCriteria criteria) {
        StringBuilder sql = new StringBuilder(
                "select * from order_details od where 1=1");// added default
        // where clause 1=1
        // to add where
        // condition for
        // this query.

        List<Object> parameters = new ArrayList<Object>();

        if (criteria.getProviderId() != null) {
            sql.append(" and od.user_id=?");
            parameters.add(criteria.getProviderId());
        }
        if (criteria.getDateFrom() != null && criteria.getDateTo() != null) {
            sql.append(" and od.date between ? and ?");
            parameters.add(criteria.getDateFrom());
            parameters.add(criteria.getDateTo());
        }
        if (criteria.getOrderName() != null
                && !criteria.getOrderName().equals("")) {
            sql.append(" and od.name like ?");
            parameters.add("%" + criteria.getOrderName() + "%");
        }
        if (criteria.getOrderDate() != null) {
            sql.append(" and Date(od.date)=?");
            parameters.add(criteria.getOrderDate());
        }
        if (criteria.getStatus() != null) {
            sql.append(" and od.order_status_id=?");
            parameters.add(criteria.getStatus().getId());
        }

        if (logger.isDebugEnabled()) {
            logger.debug("QUERY=" + sql.toString());
        }
        return getJdbcTemplate().query(sql.toString(), parameters.toArray(),
                new OrderMapper());
    }

    @Override
    public Order insertOrder(Order order) {
        SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(
                getJdbcTemplate()).withTableName("order_details")
                .usingGeneratedKeyColumns("order_id");
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("name", order.getName() == null ? "" : order.getName());
        parameters.put("date", Calendar.getInstance());
        parameters.put("order_status_id", order.getOrderStatus().getId());
        parameters.put("user_id", order.getServiceProviderId());
        parameters.put("shipping_tips", order.getTips() == null ? "" : order
                .getTips());
        parameters.put("shipping_mode", order.getMode() == null ? "" : order
                .getMode());
        parameters.put("shipping_instructions",
                order.getInstructions() == null ? "" : order.getInstructions());
        parameters.put("order_assigned_to",
                order.getOrderAssignedTo() != null ? order.getOrderAssignedTo()
                        .getId() : null);
        Number id = simpleJdbcInsert.executeAndReturnKey(parameters);
        order.setId(id.intValue());
        return order;
    }

    @Override
    public void updateOrder(Order order) {
        String sql = "Update order_details set name=?,date=?,order_status_id=?,user_id=?,shipping_mode=?,shipping_tips=?,shipping_instructions=?,order_assigned_to=? where order_id=?";
        Integer orderAssignToId = order.getOrderAssignedTo() != null ? order
                .getOrderAssignedTo().getId() : null;
        getJdbcTemplate().update(
                sql,
                new Object[]{order.getName(), order.getDate(),
                        order.getOrderStatus().getId(),
                        order.getServiceProviderId(), order.getMode(),
                        order.getTips(), order.getInstructions(),
                        orderAssignToId, order.getId()});
    }

    private void deleteOrder(List<Integer> ids) {
        if (ids.isEmpty()) {
            return;
        }
        StringBuffer buf = new StringBuffer(
                "delete from order_details where order_id in (");
        String concat = "";
        for (Integer id : ids) {
            buf.append(concat).append(id);
            concat = ",";
        }
        buf.append(")");
        getJdbcTemplate().execute(buf.toString());
    }

    @Override
    public void deleteOrder(int orderId) {
        String sql = "delete from order_details where order_id=" + orderId;
        getJdbcTemplate().execute(sql);
    }

    @SuppressWarnings("rawtypes")
    @Override
    public void deleteByBatchId(List<Integer> deletedBatchId) {
        if (deletedBatchId.isEmpty()) {
            return;
        }
        StringBuffer query = new StringBuffer(
                "select distinct(order_id) from batch where batch_id in (");
        String concat = "";
        for (Integer batchId : deletedBatchId) {
            query.append(concat).append(batchId);
            concat = ",";
        }
        query.append(")");

        List list = getJdbcTemplate().queryForList(query.toString());

        List<Integer> ids = new ArrayList<Integer>();
        for (Object obj : list) {
            Map row = (Map) obj;
            Object orderId = row.get("order_id");
            if (orderId != null) {
                ids.add(Integer.valueOf((String.valueOf(orderId))));
            }
        }

        deleteOrder(ids);
    }

    @Override
    public void deleteByBatchId(Integer batchId) {
        String sql = String.format(
                "select order_id from batch where batch_id=%d", batchId);
        Integer orderId = getJdbcTemplate().queryForInt(sql);
        if (orderId != null) {
            deleteOrder(orderId);
        }
    }

    @Override
    public void saveOrderComments(String comment, int orderID) {
        String sql = "insert into order_comments (name, order_id) values(?,?)";
        getJdbcTemplate().update(sql, new Object[]{comment, orderID});
    }

    @Override
    public void updateComment(int commentId, String newComment) {
        String sql = "Update order_comments set name=? where id=?";
        getJdbcTemplate().update(sql, new Object[]{newComment, commentId});
    }

    @Override
    public void deleteComment(int commentId) {
        String sql = "delete from order_comments where id=" + commentId;
        getJdbcTemplate().execute(sql);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> findComments(int orderId) {
        String sql = "select name from order_comments where order_id=?";
        return getJdbcTemplate().query(sql, new Object[]{orderId},
                new RowMapper() {

                    @Override
                    public Object mapRow(ResultSet resultSet, int i)
                            throws SQLException {
                        return resultSet.getString("name");
                    }
                });
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> findAllProviders() {
        // FIXME: [Wrong Service] should be moved to another service
        String sql = "SELECT * FROM user where role=" + RoleUtils.ID_SP_ROLE;
        return getJdbcTemplate().query(sql, new UserMapper());
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Page<Order> findOrderByUserId(int userId, int pageNumber,
                                         int pageSize, String sortColumn, String sortOrder) {
        PaginationHelper<Order> ph = new PaginationHelper<Order>();
        String sqlCount = "SELECT count(*) FROM order_details where user_id="
                + userId;
        String sqlQuery = "select * from order_details where user_id=" + userId
                + " order by " + sortColumn + " " + sortOrder;
        Object[] args = {};
        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args,
                pageNumber, pageSize, sortColumn, sortOrder, new OrderMapper());
    }

    /**
     * Order filters are used by admin to search orders. Filters can be created
     * by admin and can be saved. This function returns orders according to the
     * selected filter.
     */

    @Override
    @SuppressWarnings("unchecked")
    public List<Order> getAllUsingFilter(List<OrderFilterConditionVO> list) {
        String sql = "select * from order_details";
        SimpleDateFormat parser = new SimpleDateFormat("dd/MM/yyyy");
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        int index = 0;
        if (list.size() > 0)
            sql += " WHERE ";
        try {
            for (OrderFilterConditionVO item : list) {
                if (item.getOrderConditionField().toString().equalsIgnoreCase(
                        "DATE_FROM"))
                    sql += " date > '"
                            + formatter.format(parser.parse(item
                            .getConditionValue())) + "'";
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("DATE_TO"))
                    sql += " date < '"
                            + formatter.format(parser.parse(item
                            .getConditionValue())) + "'";
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("PROVIDER")
                        && item.getConditionType().toString().equalsIgnoreCase(
                        "is"))
                    sql += " user_id =" + item.getConditionValue();
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("PROVIDER")
                        && item.getConditionType().toString().equalsIgnoreCase(
                        "is not"))
                    sql += " user_id <>" + item.getConditionValue();
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("STATUS")
                        && item.getConditionType().toString().equalsIgnoreCase(
                        "is"))
                    sql += " order_status_id=" + item.getConditionValue();
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("STATUS")
                        && item.getConditionType().toString().equalsIgnoreCase(
                        "is not"))
                    sql += " order_status_id <>" + item.getConditionValue();
                index += 1;
                if (index < list.size())
                    sql += " AND ";

            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return getJdbcTemplate().query(sql, new OrderMapper());
    }

    @Override
    public Page<Order> getAllUsingFilter(List<OrderFilterConditionVO> list,
                                         int pageNumber, int recordSize, String sortColumn, String sortOrder) {
        String sql = "select * from order_details";
        SimpleDateFormat parser = new SimpleDateFormat("dd/MM/yyyy");
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        int index = 0;
        String where = "";
        if (list.size() > 0)
            where += " WHERE ";
        try {
            for (OrderFilterConditionVO item : list) {
                if (item.getOrderConditionField().toString().equalsIgnoreCase(
                        "DATE_FROM"))
                    where += " date > '"
                            + formatter.format(parser.parse(item
                            .getConditionValue())) + "'";
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("DATE_TO"))
                    where += " date < '"
                            + formatter.format(parser.parse(item
                            .getConditionValue())) + "'";
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("PROVIDER")
                        && item.getConditionType().toString().equalsIgnoreCase(
                        "is"))
                    where += " user_id =" + item.getConditionValue();
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("PROVIDER")
                        && item.getConditionType().toString().equalsIgnoreCase(
                        "is not"))
                    where += " user_id <>" + item.getConditionValue();
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("STATUS")
                        && item.getConditionType().toString().equalsIgnoreCase(
                        "is"))
                    where += " order_status_id=" + item.getConditionValue();
                else if (item.getOrderConditionField().toString()
                        .equalsIgnoreCase("STATUS")
                        && item.getConditionType().toString().equalsIgnoreCase(
                        "is not"))
                    where += " order_status_id <>" + item.getConditionValue();
                index += 1;
                if (index < list.size())
                    where += " AND ";

            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        PaginationHelper<Order> ph = new PaginationHelper<Order>();
        String sqlCount = " SELECT count(*) FROM order_details " + where;
        sql += where + "  order by " + sortColumn + " " + sortOrder;
        Object[] args = {};
        return ph.fetchPage(getJdbcTemplate(), sqlCount, sql, args, pageNumber,
                recordSize, sortColumn, sortOrder, new OrderMapper());
    }

    @Override
    public Page<Order> findOrderBySearchCriteria(OrderSearchCriteria criteria,
                                                 int pageNumber, int recordSize, String sortColumn, String sortOrder) {
        StringBuilder sql = new StringBuilder(
                "select * from order_details od where 1=1");// added default
        // where clause 1=1
        // to add where
        // condition for
        // this query.
        StringBuilder where = new StringBuilder(" ");
        List<Object> parameters = new ArrayList<Object>();

        if (criteria.getProviderId() != null) {
            where.append(" and od.user_id=?");
            parameters.add(criteria.getProviderId());
        }
        if (criteria.getDateFrom() != null && criteria.getDateTo() != null) {
            where.append(" and od.date between ? and ?");
            parameters.add(criteria.getDateFrom());
            parameters.add(criteria.getDateTo());
        }
        if (criteria.getOrderName() != null
                && !criteria.getOrderName().equals("")) {
            where.append(" and od.name like ?");
            parameters.add("%" + criteria.getOrderName() + "%");
        }
        if (criteria.getOrderDate() != null) {
            where.append(" and Date(od.date)=?");
            parameters.add(criteria.getOrderDate());
        }
        if (criteria.getStatus() != null) {
            where.append(" and od.order_status_id=?");
            parameters.add(criteria.getStatus().getId());
        }
        sql.append(where.toString());
        if (logger.isDebugEnabled()) {
            logger.debug("QUERY=" + sql.toString());
        }
        PaginationHelper<Order> ph = new PaginationHelper<Order>();
        String sqlCount = "SELECT count(*) FROM order_details od where 1=1 "
                + where.toString();
        return ph.fetchPage(getJdbcTemplate(), sqlCount, sql.toString()
                + " order by " + sortColumn + " " + sortOrder, parameters
                .toArray(), pageNumber, recordSize, sortColumn, sortOrder,
                new OrderMapper());
    }
    
    @SuppressWarnings("unchecked")
	public Integer findUnplacedOrderForUser(Integer userId) throws Exception{
    	String sql = "select * from order_details where user_id =? and order_status_id =? ";
    	List<Order> orderList = (List<Order>) getSimpleJdbcTemplate().query(sql, new OrderMapper(), userId,AppSettings.ORDER_STATUS_UNPLACED_ID);
    	
    	if(orderList == null || orderList.size()==0 ){
    		throw new ObjectRetrievalFailureException("Unplaced Order not found with userid="+userId, orderList);
    	}
    	return orderList.get(0).getId();
    }

}
